/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package mock.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import mock.dto.AccountDTO;
import mock.dto.CardDTO;
import mock.utility.Utility;

public class AccountDAOImp implements AccountDAO {
	
	private Utility util = new Utility();
	
	/**
	 * input: accountID
	 * output: AccountDTO
	 */
	@Override
	public AccountDTO getAccountByAccountID(int accountID) {
		Connection con = util.createConnection();
		if(con != null){
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = con.prepareStatement("select * from Account where AccountID = ?");
				ps.setInt(1, accountID);
				rs = ps.executeQuery();
				if(rs.next()){
					int accountid = rs.getInt("AccountID");
					int custid = rs.getInt("CustID");
					int odid = rs.getInt("ODID");
					int wdid = rs.getInt("WDID");
					double balance = rs.getDouble("Balance");
					AccountDTO account = new AccountDTO(accountid, custid, odid, wdid, balance);
					return account;
				}else{
					return null;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}finally{
				if(con != null)
					try {
						con.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				if(ps != null)
					try {
						ps.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				if(rs != null)
					try {
						rs.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
			}
		}else{
			return null;
		}
	}
	
	/**
	 * input: accountID
	 * update new balance
	 */
	@Override
	public boolean updateNewBalanceByAccountID(int accountID, double newBalance) {
		// TODO Auto-generated method stub
		Connection con = util.createConnection();
		if(con != null){
			PreparedStatement ps = null;
			try {
				ps = con.prepareStatement("update Account set Balance = ? where AccountID = ?");
				ps.setDouble(1, newBalance);
				ps.setInt(2, accountID);
				int test = ps.executeUpdate();
				if(test == 1) return true;
				else{
					return false;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				//e.printStackTrace();
				System.out.println("Update is fail!");
				return false;
			}finally{
				if(con != null)
					try {
						con.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				if(ps != null)
					try {
						ps.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
			}
		}else{
			System.out.println("Connection is null");
			return false;
		}
	}
	
	/**
	 * lay account bang CardDTO
	 */
	@Override
	public AccountDTO getAccountByCardDTO(CardDTO card) {
		// TODO Auto-generated method stub
		return getAccountByAccountID(card.getAccountID());
	}
	
	/**
	 * lay account bang cardNo
	 */
	@Override
	public AccountDTO getAccountByCardNo(String cardNo) {
		// TODO Auto-generated method stub
		Connection con = util.createConnection();
		if(con != null){
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = con.prepareStatement("select * from Card c, Account a where c.CardNo = ? and a.AccountID = c.AccountID");
				ps.setString(1, cardNo);
				rs = ps.executeQuery();
				if(rs.next()){
					int accountid = rs.getInt("AccountID");
					int custid = rs.getInt("CustID");
					int odid = rs.getInt("ODID");
					int wdid = rs.getInt("WDID");
					double balance = rs.getDouble("Balance");
					AccountDTO account = new AccountDTO(accountid, custid, odid, wdid, balance);
					return account;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				//e.printStackTrace();
				System.out.println("PS bi loi!");
			}finally{
				if(con != null)
					try {
						con.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				if(ps != null)
					try {
						ps.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
			}
		}
		return null;
	}
	
}
